package com.iqoo.store.dao;

import com.iqoo.store.model.CartItem;
import com.iqoo.store.utils.DataSourceUtils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CartDao {

    private static ComboPooledDataSource dataSource = DataSourceUtils.getDataSource();

    /**
     * 根据用户ID和商品ID查找购物车项
     *
     * @param userId    用户ID
     * @param productId 商品ID
     * @return 找到的购物车项，如果未找到则返回 null
     */
    public CartItem findByUserIdAndProductId(int userId, int productId) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        CartItem cartItem = null;

        String sql = "SELECT * FROM cart_items WHERE user_id = ? AND product_id = ?";

        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, userId);
            stmt.setInt(2, productId);
            rs = stmt.executeQuery();

            if (rs.next()) {
                cartItem = new CartItem();
                cartItem.setId(rs.getInt("id"));
                cartItem.setUserId(rs.getInt("user_id"));
                cartItem.setProductId(rs.getInt("product_id"));
                cartItem.setQuantity(rs.getInt("quantity"));
                cartItem.setCreatedAt(rs.getTimestamp("created_at"));
                cartItem.setUpdatedAt(rs.getTimestamp("updated_at"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            // 可以添加日志记录或抛出自定义异常
        } finally {
            // 关闭资源
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return cartItem;
    }

    /**
     * 保存购物车项到数据库
     *
     * @param cartItem 购物车项对象
     */
    public void add(CartItem cartItem) {
        Connection conn = null;
        PreparedStatement stmt = null;

        String sql = "INSERT INTO cart_items (user_id, product_id, quantity) VALUES (?, ?, ?)";

        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            stmt.setInt(1, cartItem.getUserId());
            stmt.setInt(2, cartItem.getProductId());
            stmt.setInt(3, cartItem.getQuantity());

            int affectedRows = stmt.executeUpdate();

            if (affectedRows == 0) {
                throw new SQLException("保存购物车项失败，数据库没有更新任何行。");
            }

            // 获取生成的主键
            try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    cartItem.setId(generatedKeys.getInt(1));
                } else {
                    throw new SQLException("保存购物车项失败，未生成主键。");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            // 可以添加日志记录或抛出自定义异常
        } finally {
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 更新购物车项
     *
     * @param cartItem 购物车项对象
     */
    public void update(CartItem cartItem) {
        Connection conn = null;
        PreparedStatement stmt = null;

        String sql = "UPDATE cart_items SET quantity = ?, updated_at = NOW() WHERE id = ?";

        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, cartItem.getQuantity());
            stmt.setInt(2, cartItem.getId());

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            // 可以添加日志记录或抛出自定义异常
        } finally {
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 删除购物车项
     *
     * @param cartItem 购物车项对象
     */
    /**
     * 根据ID查找购物车项
     *
     * @param cartId 购物车项ID
     * @return 购物车项对象，如果未找到则返回 null
     */
    public CartItem findById(int cartId) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        CartItem cartItem = null;

        String sql = "SELECT * FROM cart_items WHERE id = ?";

        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, cartId);
            rs = stmt.executeQuery();

            if (rs.next()) {
                cartItem = new CartItem();
                cartItem.setId(rs.getInt("id"));
                cartItem.setUserId(rs.getInt("user_id"));
                cartItem.setProductId(rs.getInt("product_id"));
                cartItem.setQuantity(rs.getInt("quantity"));
                cartItem.setCreatedAt(rs.getTimestamp("created_at"));
                cartItem.setUpdatedAt(rs.getTimestamp("updated_at"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            // 可以添加日志记录或抛出自定义异常
        } finally {
            // 关闭资源
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return cartItem;
    }

    /**
     * 删除购物车项
     *
     * @param cartItem 购物车项对象
     */
    public void delete(CartItem cartItem) {
        Connection conn = null;
        PreparedStatement stmt = null;

        String sql = "DELETE FROM cart_items WHERE id = ?";

        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, cartItem.getId());

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            // 可以添加日志记录或抛出自定义异常
        } finally {
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public List<CartItem> getCartItemsByUserId(int userId) {
        List<CartItem> cartItems = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        String sql = "SELECT * FROM cart_items WHERE user_id = ?";

        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, userId);
            rs = stmt.executeQuery();

            while (rs.next()) {
                CartItem cartItem = new CartItem();
                cartItem.setId(rs.getInt("id"));
                cartItem.setUserId(rs.getInt("user_id"));
                cartItem.setProductId(rs.getInt("product_id"));
                cartItem.setQuantity(rs.getInt("quantity"));
                cartItem.setCreatedAt(rs.getTimestamp("created_at"));
                cartItem.setUpdatedAt(rs.getTimestamp("updated_at"));
                cartItems.add(cartItem);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            // 可以添加日志记录或抛出自定义异常
        } finally {
            // 关闭资源
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return cartItems;
    }

}
